﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SqlserverHelper;
using System.Threading;

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using OJ;
using DLLmySQL;

/// <summary>
/// 数据分析-简单查询商品上周、本周销量
/// </summary>
public partial class getDataSnap : System.Web.UI.Page
{

    public string strCnn = ConfigurationSettings.AppSettings["DataBaseString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        string user_identity = "";
        // 用户权限
        if (Request.Form["user_identity"] != null && Request.Form["user_identity"].ToString().Trim() != "")
        {
            user_identity = Request.Form["user_identity"].ToString();
        }

        string stResult = f_query(user_identity);
        Response.Write(stResult);
    }

    public string f_query(string user_identity)
    {
        Define.retDataSnapResult ret = new Define.retDataSnapResult();
        List<Define.DataSnapResult> L = new List<Define.DataSnapResult>();
        try
        {

            //二硫化硒洗剂	200g:2.5%/瓶,塑料瓶，1瓶/盒
            //二硫化硒洗剂  150g×1瓶 / 瓶,塑料瓶
            //盐酸拉贝洛尔片 50mg×15片×2板 / 盒,铝塑板

            // 1、查询指定商品 - 盐酸拉贝洛尔片


            string strQuery = String.Format(@"	
                            
                                        SELECT 
                                            dsh_prd.prd_name, 
                                            dsh_prd.product_code, 
                                            dsh_prd.product_name, 
                                            dsh_prd.product_spec,
                                            IFNULL(last_week.last_week_sales, 0) last_week_sales,
                                            IFNULL(this_week.this_week_sales, 0) this_week_sales
                                        FROM dsh_prd
                                        LEFT JOIN
                                        (
                                            SELECT 
                                                dsnfxcs.prd_name, 
                                                dsnfxcs.prd_spec,
                                                IFNULL(SUM(dsnfxcs.sell_qty), 0) AS last_week_sales
                                            FROM 
                                                dsnfxcs ,dsh_prd 
                                            WHERE 1=1
                                                and dsh_prd.user_identity = '{0}'
                                                AND dsnfxcs.prd_name = dsh_prd.product_name AND dsnfxcs.prd_spec = dsh_prd.product_spec
                                                AND YEARWEEK(dsnfxcs.ymd, 1) = YEARWEEK(CURDATE() - INTERVAL 1 WEEK, 1)
                                            GROUP BY 
                                                dsnfxcs.prd_name, dsnfxcs.prd_spec
                                        ) AS last_week
                                        ON dsh_prd.product_name = last_week.prd_name AND dsh_prd.product_spec = last_week.prd_spec

                                        Left JOIN
                                        (
                                            SELECT 
                                                dsnfxcs.prd_name, 
                                                dsnfxcs.prd_spec,
                                                IFNULL(SUM(dsnfxcs.sell_qty), 0) AS this_week_sales
                                            FROM 
                                                dsnfxcs ,dsh_prd 
                                            WHERE 1=1
                                                and dsh_prd.user_identity = '{0}'
                                                AND dsnfxcs.prd_name = dsh_prd.product_name AND dsnfxcs.prd_spec = dsh_prd.product_spec
                                                AND YEARWEEK(ymd, 1) = YEARWEEK(CURDATE(), 1)
                                            GROUP BY 
                                                dsnfxcs.prd_name, dsnfxcs.prd_spec
    
                                        ) AS this_week
                                        ON dsh_prd.product_name = this_week.prd_name AND dsh_prd.product_spec = this_week.prd_spec
                                        where dsh_prd.user_identity = '{0}';  ", user_identity);
            DataTable DT = CmySQL.GetDataTbale(strCnn, strQuery);



            if (DT != null && DT.Rows.Count > 0)
            {

                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    Define.DataSnapResult r = new Define.DataSnapResult();
                    r.prd_name = DT.Rows[i][0].ToString().Trim();
                    r.product_code = DT.Rows[i][1].ToString().Trim();
                    r.product_name = DT.Rows[i][2].ToString().Trim();
                    r.product_spec = DT.Rows[i][3].ToString().Trim();
                    r.last_week_sales = DT.Rows[i][4].ToString().Trim();
                    r.this_week_sales = DT.Rows[i][5].ToString().Trim();

                    L.Add(r);
                }


            }

            


            ret.code = 200;
            ret.msg = "success";

            ret.data = L;

        }
        catch
        {
            ret.code = 20000;
            ret.msg = "服务不可用";
            ret.data = null;
        }

        return Ojson.ObjectToJson(ret);
    }
}